"""Header_Information
------------------------------------
    File_Name: record
    Author: yangzhide
    Date: 2024/10/12
    Description: 
------------------------------------
"""
"""tortoise相关
# 查询相关
1.查询成员：Student.all()
    列表模型对象：[<Student: 1>, <Student: 2>, <Student: 3>,...]

2.过滤成员：Student.fill(name="张三")
    列表模型对象：[<Student: 1>, ,...]

3.模糊查询：Student.fill(sno__gt="1001")
    列表模型对象：[<Student: 1>, ,...]

4.get查询：Student.get(name="王五")
    模型类型对象：<Student>

5. values查询：Student.fill().values("name","sno",...)
    列表字典对象：[{"name":"","sno":"..."},{"name":"","sno":"..."}], 只包含指定的列

students = await Student.all()  # QuerySet 对象 [student(),student(),...]
    students2 = await Student.filter(sno__gt="1001")
    students3 = await Student.get(name="张三")
    print(students)
    print(students2)
    print(students3)
    print('students::', type(students), type(students[0]), students)
    print('students2::', type(students2), type(students[0]), students2)
    print('students3:', type(students3), students3)
    st2 = await Student.filter().values('id', 'name', 'sno')  # [{},{},...]
    print('st2::', type(st2), type(st2[0]), st2)

s_item = student_in.dict()
# 剔除多对多关联字段, 剔除多个字段该方法方便
# not_c_item = {key: value for key, value in s_item.items() if key not in ['courses']}
# 剔除单个字段直接用 any_dict.pop("name"),就地删除 name, 并返回就name的值value

# 事物相关
# raise ValueError("999cccc") # 主动报错，事物回滚
# z1 = await student.get(name="zz")  # 查询无数据报错，事物回滚
# await conn.rollback()  # 不需要手动回滚，会自动回滚
# 事物————上下文管理
    # async with in_transaction() as conn:
    #     try:
    #         # 保存学生信息,并返回学生信息
    #         student = await Student.create(**not_c_item)
    #         # 保存学生多对多关联信息
    #         choose_courses = await Course.filter(id__in=student_in.courses)
    #         # student.courses 指向关联表 student_courses
    #         z1 = await student.get(name="zz")  # 查询无数据报错，事物回滚
    #         await student.courses.add(*choose_courses)  # * 打散传递
    #         return student
    #     except Exception as e:
    #         raise HTTPException(status_code=500, detail=str(e))


"""

"""连接2个数据库操作
    try:
        lmis_17_conn, lmis_17_corsur = OracleDBPool(OTHER_DB_CONFIG['lmis_cs']).get_conn()
        tpl_17_conn, tpl_17_corsur = OracleDBPool(OTHER_DB_CONFIG['tpl_17']).get_conn()
        try:
            lmis_17_corsur.execute(sql)
            tpl_17_corsur.execute(sql2)
            print('11111')
        except Exception as e:
            print('路径函数报错了...', str(e))
            lmis_17_conn.rollback()
            tpl_17_conn.rollback()
            raise HTTPException(status_code=500, detail=str(e))
        else:
            print('22222')
            lmis_17_conn.commit()
            tpl_17_conn.commit()
        finally:
            print('33333')
            lmis_17_conn.close()
            tpl_17_conn.close()
        return "123"
    except Exception as e:
        print('最外层报错了', str(e))
        print('traceback', traceback.format_exc())  # 打印较全的异常信息
        raise HTTPException(status_code=500, detail=str(e))
"""

"""1个oralce连接——上下文管理
sql = ' update y1 set name = :name,city=:city where id = :id '
sql2 = ' select * from y1 '
args = {"name": "麻溜", "city": "湖南", "id": 2}
with OracleContext(OTHER_DB_CONFIG['lmis_cs']).get_conn() as conn:
    print('cccc:::', conn)
    try:
        print(111)
        cur = conn.cursor()
        cur.execute(sql, args)
        row = fetch_all_dict(cur, sql2)
    except Exception as e:
        print(traceback.print_exc())
        print('出错了！！！', str(e))
        conn.rollback()
        raise e
    else:
        print(222)
        conn.commit()
    finally:
        print(333)
        conn.close()
    return {"status": 200, "data": row}
"""

"""tortoise-orm相关查询
1. 返回一个dict:① get,②get_or_none,③ filter.values("name",...)
    get: 没有数据时，报错 "Object \"Student\" does not exist"
    students = await Student.get(sno='10022')
    get_or_none： 查询无结果则返回 None
    students = await Student.get_or_none(sno='10022')

2. 返回一对多中的列：①字符串， ②列表
    ①字符串：students = await Student.all().values("name", "sno", "clas__name")
    ②列表：
    students = await Student.all().prefetch_related("clas")
    print('students:', students, type(students))
    all_data = []
    for student in students:
        student_dict = {
            'name': student.name,
            'sno': student.sno,  # 确保你的 Student 模型中有 sno 字段
            # 如果 clas 是一个外键，则直接获取关联对象的属性
            'clas_id': student.clas,
            'clas_name': student.clas.name if student.clas else None,
        }
        # 由于使用了 prefetch_related，这里不需要再查询 clas
        # 如果你确实需要 clas 的字典表示，可以这样做：
        # clas_dict = dict(student.clas) if student.clas else None
        # 但注意，这里 dict(student.clas) 可能仍然不起作用，因为 clas 可能不是简单的字典可序列化对象
        # 因此，我们已经在上面直接获取了需要的 clas 属性

        # 添加到结果列表中
        all_data.append(student_dict)
    return all_data

"""

################################################################################
